package util;

import java.io.FileOutputStream;
import java.io.IOException;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;







import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;




public class ExcelUtil {
	private static Logger logger = LogManager.getLogger(ExcelUtil.class);
	public static void jdbcex(String path,ResultSet rs) throws InstantiationException, IllegalAccessException, 
	    ClassNotFoundException, SQLException, IOException, InterruptedException {
		System.out.println("开始导出"+path);
	    //内存中只创建100个对象，写临时文件，当超过100条，就将内存中不用的对象释放。
	    Workbook wb = new SXSSFWorkbook(100);            //关键语句
	    Sheet sheet = null;        //工作表对象
	    Row nRow = null;        //行对象
	    Cell nCell = null;        //列对象
   
	    ResultSetMetaData rsmd = rs.getMetaData();
	    long  startTime = System.currentTimeMillis();    //开始时间
	    logger.info("strat execute time: " + startTime);

	    int rowNo = 0;        //总行号
	    int pageRowNo = 0;    //页行号
        int count=0;
	    while(rs.next()) {
	        //打印50000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以
	    	count++;
	    	 if(rowNo%50000==0){  
	             System.out.println("Current Sheet:" + rowNo/50000);  
	             sheet = wb.createSheet("第"+(rowNo/50000+1)+"个工作簿");//建立新的sheet对象  
	             sheet = wb.getSheetAt(rowNo/50000);        //动态指定当前的工作表  
	             pageRowNo = 0;      //每当新建了工作表就将当前工作表的行号重置为0  
	         }     
	         rowNo++;  
	         nRow = sheet.createRow(pageRowNo++);    //新建行对象  
	   
	         // 打印每行，每行有6列数据   rsmd.getColumnCount()==6 --- 列属性的个数  
	         for(int j=0;j<rsmd.getColumnCount();j++){  
	             nCell = nRow.createCell(j);  
	             nCell.setCellValue(rs.getString(j+1));  
	         }  
	               
	         if(rowNo%10000==0){  
	             System.out.println("row no: " + rowNo);  
	         }  
       Thread.sleep(1);    //休息一下，防止对CPU占用，其实影响不大  
	     }  
	           
	    long finishedTime = System.currentTimeMillis(); //处理完成时间  
	    System.out.println("finished execute  time: " + (finishedTime - startTime)/1000 + "m");  
	    if(count>0){
	    	 FileOutputStream fOut = new FileOutputStream(path);  
	 	    wb.write(fOut);  
	 	    fOut.flush();       //刷新缓冲区  
	 	    fOut.close();  
	 	   long stopTime = System.currentTimeMillis();     //写文件时间  
	 	  logger.info("write xlsx file time: " + (stopTime - startTime)/1000 + "m");  
	    }else{
	    	logger.info("没有数据返回");
	    }


	}
}
